{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d514361c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "from sqlalchemy import create_engine\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffd1dc11",
   "metadata": {},
   "outputs": [],
   "source": [
    "list = [谷物类主要包括：大豆、玉米、豆粕、菜粕\n",
    "　　农副类：白糖、棉花、鸡蛋、棕榈、豆油\n",
    "　　能源类：铁矿石、动力煤、焦煤、焦炭、甲醇\n",
    "　　金属类：锌、白银、黄金、铜、螺纹\n",
    "　　化工类：PTA、纤板、玻璃、PP、橡胶、夹板、塑料、PVC\n",
    "　　金融类：股指、国债]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d108ca6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "path ='F:\\\\HQData\\\\future_basic'\n",
    "file = 'trading_code.txt'\n",
    "\n",
    "file_path = os.path.join(path, file)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "8ec202aa",
   "metadata": {},
   "source": [
    "fo = open(file_path, \"r\")\n",
    "\n",
    "for line in fo.readlines():                          #依次读取每行  \n",
    "    line = line.strip()                             #去掉每行头尾空白  \n",
    "    # print (\"读取的数据为: %s\" % (line))\n",
    "    cells = line.split(\"：\")\n",
    "    for cell in cells[1].split('、'):\n",
    "        print(cell)\n",
    "fo.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "684e2595",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['谷物类包括', '大豆、玉米、豆粕、菜粕']\n",
      "['农副类', '白糖、棉花、鸡蛋、棕榈、豆油']\n",
      "['能源类', '铁矿石、动力煤、焦煤、焦炭、甲醇']\n",
      "['金属类', '锌、白银、黄金、铜、螺纹']\n",
      "['化工类', 'PTA、纤板、玻璃、PP、橡胶、夹板、塑料、PVC']\n",
      "['金融类', '股指、国债']\n"
     ]
    }
   ],
   "source": [
    "fo = open(file_path, \"r\")\n",
    "columns = ['contract_type', 'code_name', 'ts_code', 'fut_code']\n",
    "columns = ['contract_type', 'code_name']\n",
    "df = pd.DataFrame(columns=columns)\n",
    "i = 0\n",
    "\n",
    "for line in fo.readlines():                          #依次读取每行  \n",
    "    line = line.strip()                             #去掉每行头尾空白  \n",
    "    # print (\"读取的数据为: %s\" % (line))\n",
    "    cells = line.split(\"：\")\n",
    "    if cells.__len__() < 2:\n",
    "        continue\n",
    "    print(cells)\n",
    "    for cell in cells[1].split('、'):\n",
    "        i= i + 1\n",
    "        # pd.insert({'code_type':cells[0], 'code': cell}, ignore_index=True)\n",
    "        df.loc[i] = [cells[0], cell]\n",
    "fo.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "b94a5f21",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd = pd.DataFrame(columns=['code_type', 'code'])\n",
    "\n",
    "# pd.insert(pd.shape(0), column=['code_type', 'code'], value=[cells[0], cell])\n",
    "# 列： pd.insert(1, column=['code_type', 'code'], value=[cells[0], cell])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "1c36c3e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all.to_csv(os.path.join(path, 'trading_code.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9ac1f2d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contract_type</th>\n",
       "      <th>code_name</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>大豆</td>\n",
       "      <td>大豆主力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>玉米</td>\n",
       "      <td>玉米主力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>豆粕</td>\n",
       "      <td>豆粕主力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>菜粕</td>\n",
       "      <td>菜粕主力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>农副类</td>\n",
       "      <td>白糖</td>\n",
       "      <td>白糖主力</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  contract_type code_name  name\n",
       "1         谷物类包括        大豆  大豆主力\n",
       "2         谷物类包括        玉米  玉米主力\n",
       "3         谷物类包括        豆粕  豆粕主力\n",
       "4         谷物类包括        菜粕  菜粕主力\n",
       "5           农副类        白糖  白糖主力"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['name'] = df['code_name']\n",
    "df['name'] = df['code_name'] + '主力'\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "512324b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "dbserver = 'mysql+pymysql://dbuser:dbuser@121@rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com:3306/Quote?charset=utf8'\n",
    "#dbserver = 'mysql+pymysql://gmgbj:G7gIVYB9Xk^7@192.168.90.193:3306/vnpy_quote'\n",
    "engine = create_engine(dbserver)\n",
    "\n",
    "with  engine.begin()  as conn:\n",
    "    df_contracts = pd.read_sql_query('select * from contracts_main',con =conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9e741292",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contract_type</th>\n",
       "      <th>code_name</th>\n",
       "      <th>name</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>玉米</td>\n",
       "      <td>玉米主力</td>\n",
       "      <td>C.DCE</td>\n",
       "      <td>C</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>豆粕</td>\n",
       "      <td>豆粕主力</td>\n",
       "      <td>M.DCE</td>\n",
       "      <td>M</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  contract_type code_name  name ts_code symbol exchange\n",
       "0         谷物类包括        玉米  玉米主力   C.DCE      C      DCE\n",
       "1         谷物类包括        豆粕  豆粕主力   M.DCE      M      DCE"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = df.merge(df_contracts, on='name')\n",
    "df_all = df_all.drop(columns=['fut_code'])\n",
    "df_all.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b4641310",
   "metadata": {},
   "outputs": [],
   "source": [
    "with  engine.begin()  as conn:\n",
    "    df_all.to_sql(name=\"trading_code\", con =conn, if_exists=\"append\", index=True, index_label=\"trading_id\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e28848b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contract_type</th>\n",
       "      <th>code_name</th>\n",
       "      <th>name</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>玉米</td>\n",
       "      <td>玉米主力</td>\n",
       "      <td>C.DCE</td>\n",
       "      <td>C</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>豆粕</td>\n",
       "      <td>豆粕主力</td>\n",
       "      <td>M.DCE</td>\n",
       "      <td>M</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  contract_type code_name  name ts_code symbol exchange\n",
       "0         谷物类包括        玉米  玉米主力   C.DCE      C      DCE\n",
       "1         谷物类包括        豆粕  豆粕主力   M.DCE      M      DCE"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c45327b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>contract_type</th>\n",
       "      <th>code_name</th>\n",
       "      <th>name</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>玉米</td>\n",
       "      <td>玉米主力</td>\n",
       "      <td>C.DCE</td>\n",
       "      <td>C</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>豆粕</td>\n",
       "      <td>豆粕主力</td>\n",
       "      <td>M.DCE</td>\n",
       "      <td>M</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>谷物类包括</td>\n",
       "      <td>菜粕</td>\n",
       "      <td>菜粕主力</td>\n",
       "      <td>RM.ZCE</td>\n",
       "      <td>RM</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农副类</td>\n",
       "      <td>白糖</td>\n",
       "      <td>白糖主力</td>\n",
       "      <td>SR.ZCE</td>\n",
       "      <td>SR</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农副类</td>\n",
       "      <td>棉花</td>\n",
       "      <td>棉花主力</td>\n",
       "      <td>CF.ZCE</td>\n",
       "      <td>CF</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>农副类</td>\n",
       "      <td>鸡蛋</td>\n",
       "      <td>鸡蛋主力</td>\n",
       "      <td>JD.DCE</td>\n",
       "      <td>JD</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>农副类</td>\n",
       "      <td>豆油</td>\n",
       "      <td>豆油主力</td>\n",
       "      <td>Y.DCE</td>\n",
       "      <td>Y</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>能源类</td>\n",
       "      <td>铁矿石</td>\n",
       "      <td>铁矿石主力</td>\n",
       "      <td>I.DCE</td>\n",
       "      <td>I</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>能源类</td>\n",
       "      <td>动力煤</td>\n",
       "      <td>动力煤主力</td>\n",
       "      <td>ZC.ZCE</td>\n",
       "      <td>ZC</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>能源类</td>\n",
       "      <td>动力煤</td>\n",
       "      <td>动力煤主力</td>\n",
       "      <td>TC.ZCE</td>\n",
       "      <td>TC</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>能源类</td>\n",
       "      <td>焦煤</td>\n",
       "      <td>焦煤主力</td>\n",
       "      <td>JM.DCE</td>\n",
       "      <td>JM</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>能源类</td>\n",
       "      <td>焦炭</td>\n",
       "      <td>焦炭主力</td>\n",
       "      <td>J.DCE</td>\n",
       "      <td>J</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>能源类</td>\n",
       "      <td>甲醇</td>\n",
       "      <td>甲醇主力</td>\n",
       "      <td>MA.ZCE</td>\n",
       "      <td>MA</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>能源类</td>\n",
       "      <td>甲醇</td>\n",
       "      <td>甲醇主力</td>\n",
       "      <td>ME.ZCE</td>\n",
       "      <td>ME</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>金属类</td>\n",
       "      <td>白银</td>\n",
       "      <td>白银主力</td>\n",
       "      <td>AG.SHF</td>\n",
       "      <td>AG</td>\n",
       "      <td>SHFE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>金属类</td>\n",
       "      <td>黄金</td>\n",
       "      <td>黄金主力</td>\n",
       "      <td>AU.SHF</td>\n",
       "      <td>AU</td>\n",
       "      <td>SHFE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>化工类</td>\n",
       "      <td>PTA</td>\n",
       "      <td>PTA主力</td>\n",
       "      <td>TA.ZCE</td>\n",
       "      <td>TA</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>化工类</td>\n",
       "      <td>玻璃</td>\n",
       "      <td>玻璃主力</td>\n",
       "      <td>FG.ZCE</td>\n",
       "      <td>FG</td>\n",
       "      <td>CZCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>化工类</td>\n",
       "      <td>橡胶</td>\n",
       "      <td>橡胶主力</td>\n",
       "      <td>RU.SHF</td>\n",
       "      <td>RU</td>\n",
       "      <td>SHFE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>化工类</td>\n",
       "      <td>塑料</td>\n",
       "      <td>塑料主力</td>\n",
       "      <td>L.DCE</td>\n",
       "      <td>L</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>化工类</td>\n",
       "      <td>PVC</td>\n",
       "      <td>PVC主力</td>\n",
       "      <td>V.DCE</td>\n",
       "      <td>V</td>\n",
       "      <td>DCE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   contract_type code_name   name ts_code symbol exchange\n",
       "0          谷物类包括        玉米   玉米主力   C.DCE      C      DCE\n",
       "1          谷物类包括        豆粕   豆粕主力   M.DCE      M      DCE\n",
       "2          谷物类包括        菜粕   菜粕主力  RM.ZCE     RM     CZCE\n",
       "3            农副类        白糖   白糖主力  SR.ZCE     SR     CZCE\n",
       "4            农副类        棉花   棉花主力  CF.ZCE     CF     CZCE\n",
       "5            农副类        鸡蛋   鸡蛋主力  JD.DCE     JD      DCE\n",
       "6            农副类        豆油   豆油主力   Y.DCE      Y      DCE\n",
       "7            能源类       铁矿石  铁矿石主力   I.DCE      I      DCE\n",
       "8            能源类       动力煤  动力煤主力  ZC.ZCE     ZC     CZCE\n",
       "9            能源类       动力煤  动力煤主力  TC.ZCE     TC     CZCE\n",
       "10           能源类        焦煤   焦煤主力  JM.DCE     JM      DCE\n",
       "11           能源类        焦炭   焦炭主力   J.DCE      J      DCE\n",
       "12           能源类        甲醇   甲醇主力  MA.ZCE     MA     CZCE\n",
       "13           能源类        甲醇   甲醇主力  ME.ZCE     ME     CZCE\n",
       "14           金属类        白银   白银主力  AG.SHF     AG     SHFE\n",
       "15           金属类        黄金   黄金主力  AU.SHF     AU     SHFE\n",
       "16           化工类       PTA  PTA主力  TA.ZCE     TA     CZCE\n",
       "17           化工类        玻璃   玻璃主力  FG.ZCE     FG     CZCE\n",
       "18           化工类        橡胶   橡胶主力  RU.SHF     RU     SHFE\n",
       "19           化工类        塑料   塑料主力   L.DCE      L      DCE\n",
       "20           化工类       PVC  PVC主力   V.DCE      V      DCE"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a3912573",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install sqlalchemy==1.4.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27fac4e9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "id": "6b9e26cb",
   "metadata": {},
   "source": [
    "谷物类：大豆、玉米、豆粕、菜粕\n",
    "　　农副类：白糖、棉花、鸡蛋、棕榈、豆油\n",
    "　　能源类：铁矿石、动力煤、焦煤、焦炭、甲醇\n",
    "　　金属类：锌、白银、黄金、铜、螺纹\n",
    "　　化工类：PTA、纤板、玻璃、PP、橡胶、夹板、塑料、PVC\n",
    "　　金融类：股指、国债"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
